package com.frinder.base.utils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.google.common.base.Strings;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * @author frinder
 * @date 2018/10/27
 * @Description: ${todo}
 */
@Slf4j
public class ExcelHelper {

    public static final ExcelHelper INSTANCE = new ExcelHelper();

    private ExcelHelper() {
    }

    public enum Code {
        SUCCESS("导入成功！"),
        EMPTY("导入Excel为空！"),
        MAX_COUNT_ERROR("导入行数大于5000！"),
        ERROR_CELLS("导入列与模板不匹配！"),
        UN_KNOW_ERROR("导入失败！");

        private String message;

        Code(String message) {
            this.message = message;
        }
    }

    public class Result {
        private Code code;
        private String message;

        public Result(Code code) {
            this.code = code;
            message = code.message;
        }

        public Result(Code code, String message) {
            this.code = code;
            this.message = message;
        }

        public Code getCode() {
            return code;
        }

        public String getMessage() {
            return message;
        }
    }

    /**
     * xls 后缀
     * excel
     */
    private final String xlsRegex = "^.+\\.(?i)(xls)$";
    /**
     * xlsx 后缀
     * excel
     */
    private final String xlsxRegex = "^.+\\.(?i)(xlsx)$";
    /**
     * excel 内容为空
     */
    private final int emptyRowNum = 1;
    /**
     * excel 导入最大行
     */
    private final int maxRowNum = 5001;
    /**
     * 默认单元格宽度
     */
    private final int defaultColumnWidth = 17;
    /**
     * 日期格式
     */
    private final DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");

    /**
     * 导出 excel content type
     */
    private final String contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8";

    /**
     * 导出 excel
     *
     * @param response
     * @param fileName
     * @param headMap
     * @param dataList
     * @throws Exception
     */
    public void write(HttpServletResponse response, String fileName, Map<String, String> headMap, List dataList) throws Exception {
        // 导出内容
        byte[] content = getContent(headMap, dataList);
        fileName += LocalDateTime.now().format(dateTimeFormatter);
        // 设置response参数，可以打开下载页面
        response.reset();
        response.setContentType(contentType);
        String file = new String(String.format("%s.xlsx", fileName).getBytes(), "UTF-8");
        response.setHeader("Content-Disposition", String.format("attachment;filename=%s", file));
        response.setContentLength(content.length);
        // 输出流
        try (
                ServletOutputStream outputStream = response.getOutputStream();
                BufferedInputStream bis = new BufferedInputStream(new ByteArrayInputStream(content));
                BufferedOutputStream bos = new BufferedOutputStream(outputStream)
        ) {

            byte[] buff = new byte[1024];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
            outputStream.flush();
        }
    }

    /**
     * 导出Excel (.xlsx)格式
     *
     * @param headMap  属性-列头
     * @param dataList 数据集
     */
    public byte[] getContent(Map<String, String> headMap, List dataList) {
        try (
                ByteArrayOutputStream os = new ByteArrayOutputStream();
                // 声明一个工作薄
                SXSSFWorkbook workbook = new SXSSFWorkbook(1000)
        ) {
            log.info("*** 开始写入 Sheet，记录数：{}", dataList.size());
            workbook.setCompressTempFiles(true);
            //表头样式
            CellStyle titleStyle = workbook.createCellStyle();
            titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            Font titleFont = workbook.createFont();
            titleFont.setFontHeightInPoints((short) 20);
            titleFont.setBoldweight((short) 700);
            titleStyle.setFont(titleFont);
            // 列头样式
            CellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            Font headerFont = workbook.createFont();
            headerFont.setFontHeightInPoints((short) 12);
            headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            headerStyle.setFont(headerFont);
            // 单元格样式
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            Font cellFont = workbook.createFont();
            cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            cellStyle.setFont(cellFont);
            // 生成一个(带标题)表格
            SXSSFSheet sheet = createSheet(workbook, headMap.keySet());
            // 产生表格标题行,以及设置列宽
            String[] properties = new String[headMap.size()];
            String[] headers = new String[headMap.size()];
            int colIndex = 0;
            for (String m : headMap.keySet()) {
                properties[colIndex] = m;
                headers[colIndex] = headMap.get(m);
                colIndex++;
            }
            // 遍历集合数据，产生数据行
            int rowIndex = 0;
            for (Object data : dataList) {
                if (rowIndex == 65535 || rowIndex == 0) {
                    // 如果数据超过了，则在第二页显示
                    if (rowIndex != 0) {
                        sheet = createSheet(workbook, headMap.keySet());
                    }
                    // 列头 rowIndex =1
                    SXSSFRow headerRow = sheet.createRow(0);
                    for (int i = 0; i < headers.length; i++) {
                        headerRow.createCell(i).setCellValue(headers[i]);
                        headerRow.getCell(i).setCellStyle(headerStyle);
                    }
                    // 数据内容从 rowIndex=2开始
                    rowIndex = 1;
                }
                // data 转化为 json
                JSONObject obj = JSON.parseObject(JSON.toJSONStringWithDateFormat(data, "yyyy-MM-dd"));
                SXSSFRow dataRow = sheet.createRow(rowIndex);
                SXSSFCell newCell;
                for (int i = 0; i < properties.length; i++) {
                    newCell = dataRow.createCell(i);
                    newCell.setCellValue(obj.getString(properties[i]));
                    newCell.setCellStyle(cellStyle);
                }
                rowIndex++;
            }
            workbook.write(os);
            workbook.dispose();
            log.info("*** 写入 Sheet 成功，记录数：{}", dataList.size());
            return os.toByteArray();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            throw new RuntimeException(String.format("*** 写入Sheet失败，原因：", e.getMessage()), e);
        }
    }

    /**
     * 创建 sheet
     *
     * @param workbook
     * @param keySet
     * @return
     */
    private SXSSFSheet createSheet(SXSSFWorkbook workbook, Set<String> keySet) {
        SXSSFSheet sheet = workbook.createSheet();
        int colIndex = 0;
        for (String m : keySet) {
            int bytes = m.getBytes().length;
            // 设置列宽
            sheet.setColumnWidth(colIndex, ((bytes < defaultColumnWidth ? defaultColumnWidth : bytes) * 256));
            colIndex++;
        }
        return sheet;
    }

    /**
     * @param fileName
     * @param is       输入流（本方法不负责关闭流）
     * @param headMap  导入 列名->属性名 集合
     * @param clazz    对象不能使用 Date类型，请使用 String类型代替
     * @param <T>
     * @return
     * @throws IOException
     */
    public <T> Result read(List<T> results, String fileName, InputStream is, Map<String, String> headMap, Class<T> clazz) throws IOException {
        try {
            // 解析 excel
            Workbook workbook = getWorkbook(fileName, is);
            // 获取第一个 sheet
            Sheet sheet = workbook.getSheetAt(0);
            // 获取行数
            int rowCount = sheet.getPhysicalNumberOfRows();
            // 无内容
            if (rowCount <= emptyRowNum) {
                return new Result(Code.EMPTY);
            }
            // 小于5002
            if (rowCount > maxRowNum) {
                return new Result(Code.MAX_COUNT_ERROR);
            }
            // 列数
            Row row0 = sheet.getRow(0);
            int cellCount = row0.getPhysicalNumberOfCells();
            List<String> rowTitles = Lists.newArrayList();
            Cell cell;
            for (int i = 0; i < cellCount; i++) {
                rowTitles.add(String.valueOf(getCellValue(row0.getCell(i))).trim());
            }
            // 导出字段个数
            int fieldsSize = headMap.size();
            // 列标题
            String fieldName;
            for (int i = 1; i < rowCount; i++) {
                Row row = sheet.getRow(i);
                // 将行内容转化为 map
                Map<String, Object> objectMap = Maps.newHashMap();
                Set<String> actualTitles = Sets.newHashSet();
                for (int c = 0; c < cellCount; c++) {
                    if (null != (cell = row.getCell(c))) {
                        String rowTitle = rowTitles.get(c);
                        if (!Strings.isNullOrEmpty(fieldName = headMap.get(rowTitle))) {
                            objectMap.put(fieldName, getCellValue(cell));
                            actualTitles.add(rowTitle);
                        }
                    } else {
                        continue;
                    }
                }
                if (fieldsSize > objectMap.size()) {
                    Set<String> importTitles = headMap.keySet().stream().sorted().collect(Collectors.toSet());
                    actualTitles = actualTitles.stream().sorted().collect(Collectors.toSet());
                    // 清空已添加记录
                    results.clear();
                    log.error("*** 导入模板列不匹配，期望：{}，实际：{}", importTitles, actualTitles);
                    return new Result(Code.ERROR_CELLS, Code.ERROR_CELLS.message + String.format("期望：%s，实际：%s", importTitles, actualTitles));
                }
                // 获取目标对象
                T target = clazz.newInstance();
                // 复制属性值
                // spring BeanUtils.copyProperties(s,d) s不能为map，否则无法复制
                org.apache.commons.beanutils.BeanUtils.copyProperties(target, objectMap);
                //
                results.add(target);
            }
            log.info("*** 成功解析记录数：{}", results.size());
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            // 清空已添加记录
            results.clear();
            return new Result(Code.UN_KNOW_ERROR);
        }
        return new Result(Code.SUCCESS);
    }

    /**
     * 获取单元格内容
     *
     * @param cell
     * @return
     */
    private Object getCellValue(Cell cell) {
        Object value;
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
            case Cell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue();
                } else {
                    double numericCellValue = cell.getNumericCellValue();
                    if ((int) numericCellValue == numericCellValue) {
                        value = (int) numericCellValue;
                    } else {
                        value = numericCellValue;
                    }
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_FORMULA:
                value = cell.getCellFormula();
                break;
            case Cell.CELL_TYPE_ERROR:
                value = cell.getErrorCellValue();
                break;
            default:
                cell.setCellType(Cell.CELL_TYPE_STRING);
                value = cell.getStringCellValue();
                break;
        }
        return value;
    }

    /**
     * 获取 Workbook
     *
     * @param fileName
     * @param is
     * @return
     * @throws Exception
     */
    private Workbook getWorkbook(String fileName, InputStream is) throws Exception {
        Workbook workbook;
        if (fileName.matches(xlsRegex)) {
            workbook = new HSSFWorkbook(is);
        } else {
            workbook = new XSSFWorkbook(is);
        }
        return workbook;
    }

}
